﻿BEGIN TRAN
DELETE FROM dbo.[Users]
DELETE FROM dbo.[EntityType]
DELETE FROM dbo.[EntityAttribute]
DELETE FROM dbo.[Game]
DELETE FROM dbo.[GameEntity]
DELETE FROM dbo.[GameRole]
DELETE FROM dbo.[Team]
DELETE FROM dbo.[EntityAttributeType]

INSERT INTO dbo.[EntityType] (ID, Name)
SELECT 1,'Человек'
UNION ALL SELECT 2,'Машина'
UNION ALL SELECT 3,'Точка'

INSERT INTO dbo.[GameRole] (ID, Name)
SELECT 1,'Человек'
UNION ALL SELECT 2,'Машина'

--Атрибуты человека
INSERT  INTO dbo.EntityAttributeType(ID, EntityTypeID, Name)
VALUES ('FNAME', 1, 'Full Name')
INSERT  INTO dbo.EntityAttributeType(ID, EntityTypeID, Name)
VALUES ('BIRTH', 1, 'Birth Date')
INSERT  INTO dbo.EntityAttributeType(ID, EntityTypeID, Name)
VALUES ('TPHONE', 1, 'Team phone')
INSERT  INTO dbo.EntityAttributeType(ID, EntityTypeID, Name)
VALUES ('OPHONES', 1, 'Other Phones')
INSERT  INTO dbo.EntityAttributeType(ID, EntityTypeID, Name)
VALUES ('ICQ', 1, 'ICQ')
INSERT  INTO dbo.EntityAttributeType(ID, EntityTypeID, Name)
VALUES ('SKYPE', 1, 'Skype')
INSERT  INTO dbo.EntityAttributeType(ID, EntityTypeID, Name)
VALUES ('IM', 1, 'Other IM''s')
INSERT  INTO dbo.EntityAttributeType(ID, EntityTypeID, Name)
VALUES ('TRAN', 1, 'Transmitter')
INSERT  INTO dbo.EntityAttributeType(ID, EntityTypeID, Name)
VALUES ('TRACK', 1, 'Tracker')
INSERT  INTO dbo.EntityAttributeType(ID, EntityTypeID, Name)
VALUES ('DEVS', 1, 'Other Devices')
--Атрибуты машины
INSERT  INTO dbo.EntityAttributeType(ID, EntityTypeID, Name)
VALUES ('MODL', 1, 'Model')
INSERT  INTO dbo.EntityAttributeType(ID, EntityTypeID, Name)
VALUES ('SRN', 1, 'Registration number')
INSERT  INTO dbo.EntityAttributeType(ID, EntityTypeID, Name)
VALUES ('CLR', 1, 'Color')




declare @teamid int
declare @gameid int
INSERT INTO Team([Name],[Description])
VALUES ('Fusion','Best Ryazan team')
SELECT @teamid = @@identity

INSERT INTO dbo.[Entity]([Name],[Description],[TeamID],[Photo],[EntityTypeID])
SELECT 'Tatyana','Татьяна Конопкина (Деловая колбаса)',@teamid, null,1
UNION ALL SELECT 'Twinsen','Андрей Поспехов',@teamid, null,1
UNION ALL SELECT 'AlexeySch','Алексей Щеглов (Cap)',@teamid, null,1
UNION ALL SELECT 'Dima','Дмитрий Оськин',@teamid, null,1
UNION ALL SELECT 'Вжик','Экипаж Вжик',@teamid, null,2
UNION ALL SELECT 'Бегемот','Бегемот',@teamid, null,2
UNION ALL SELECT 'Ракета','Ракета',@teamid, null,2
UNION ALL SELECT 'Team_on','Team_on',@teamid, null,2
UNION ALL SELECT 'Бухенваген','Бухенваген',@teamid, null,2


exec dbo.Roles_CreateRole 'Admin'
exec dbo.Roles_CreateRole 'Viewer'
exec dbo.Roles_CreateRole 'Operator'
exec dbo.Roles_CreateRole 'Sender'

declare @UserID int 

exec Membership_CreateUser  @UserName='AlexeySch', @Password = '123456', @PasswordSalt = '', @Email = 'AlexeySch@gmail.com', @PasswordQuestion = null,
 @PasswordAnswer = null, @IsApproved = 1, @CurrentTimeUtc = '20080101', @CreateDate = '20080101', @UniqueEmail = 1, @PasswordFormat = 0, @UserId = @UserID
exec [dbo].[Profile_SetProperties] @UserName = 'AlexeySch', @TeamID = @TeamID, @IsUserAnonymous = 0, @CurrentTimeUtc = '20080101'

exec Membership_CreateUser  @UserName='sa', @Password = 'sa', @PasswordSalt = '', @Email = 'sa@gmail.com', @PasswordQuestion = null,
 @PasswordAnswer = null, @IsApproved = 1, @CurrentTimeUtc = '20080101', @CreateDate = '20080101', @UniqueEmail = 1, @PasswordFormat = 0, @UserId = @UserID output
exec [dbo].[Profile_SetProperties] @UserName = 'sa', @TeamID = @TeamID, @IsUserAnonymous = 0, @CurrentTimeUtc = '20080101'

exec Membership_CreateUser  @UserName='rzn_AlexeySch', @Password = '123456', @PasswordSalt = '', @Email = 'rzn_AlexeySch@gmail.com', @PasswordQuestion = null,
 @PasswordAnswer = null, @IsApproved = 1, @CurrentTimeUtc = '20080101', @CreateDate = '20080101', @UniqueEmail = 1, @PasswordFormat = 0, @UserId = @UserID output
exec [dbo].[Profile_SetProperties] @UserName = 'rzn_AlexeySch', @TeamID = @TeamID, @IsUserAnonymous = 0, @CurrentTimeUtc = '20080101'

exec Membership_CreateUser  @UserName='Coordinator', @Password = '1', @PasswordSalt = '', @Email = 'Coordinator@gmail.com', @PasswordQuestion = null,
 @PasswordAnswer = null, @IsApproved = 1, @CurrentTimeUtc = '20080101', @CreateDate = '20080101', @UniqueEmail = 1, @PasswordFormat = 0, @UserId = @UserID output
exec [dbo].[Profile_SetProperties] @UserName = 'Coordinator', @TeamID = @TeamID, @IsUserAnonymous = 0, @CurrentTimeUtc = '20080101'

INSERT INTO  dbo.Game(TeamID, [Name], Description, CreationDate)
VALUES (@teamid,'Game1', 'Текущая игра',GetDate())

SELECT @gameID = @@IDENTITY

INSERT INTO dbo.[GameEntity](GameID, EntityID,  Visible)
SELECT @gameID, E.ID, 1
FROM dbo.[Entity] E
WHERE E.TeamID = @teamid

INSERT INTO  dbo.Game(TeamID, [Name], Description, CreationDate)
VALUES (@teamid,'TestGame', 'Пробная игра',GetDate())

SELECT @gameID = @@IDENTITY

INSERT INTO dbo.[GameEntity](GameID, EntityID,  Visible)
SELECT @gameID, E.ID, 1
FROM dbo.[Entity] E
WHERE E.TeamID = @teamid


ROLLBACK TRAN
